package mnc.game.tifo;

import java.util.ArrayList;
import java.util.HashMap;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


/**
 * <p>
 * This class copies a SQLite database from your application's assets directory
 * to /data/data/<your_application_package>/databases/ so you can access it
 * using the SQLite APIs provided by the Android SDK. Note that
 * {@link DBFormData#copyDatabaseFile()} checks for the existence of the database
 * and only copies it if needed.
 * </p>
 * <p>
 * {@link DBFormData#copyDatabaseFile()} calls
 * {@link SQLiteOpenHelper#getReadableDatabase()}, which in turn calls
 * {@link SQLiteOpenHelper#onCreate(SQLiteDatabase)}. Be aware that the
 * implementation of the overridden
 * {@link SQLiteOpenHelper#onCreate(SQLiteDatabase)} must remain empty in order
 * for the copy operation to work correctly.
 * </p>
 * <p>
 * This class includes a constructor {@link DBFormData#DBHelper(Context, boolean)}
 * which allows you to control whether the database file should be copied when
 * the class is instantiated.
 * </p>
 * 
 * @see SQLiteOpenHelper
 */
public class DBTifoData extends SQLiteOpenHelper {
	
	private static final String DATABASE_TABLE = "tb_info";
	private static final int DATABASE_VERSION = 1;

	private static final String TAG = "tifo";
		
	private SQLiteOpenHelper mOpenHelper;
	
	private static final String DB_NAME = "tifo_info.db";
	
	/**
	 * Constructor Keeps a reference to the passed context in order to access
	 * the application's assets.
	 * 
	 * @param context
	 *            Context to be used
	 */
	public DBTifoData(Context context) {
		super(context, DB_NAME, null, DATABASE_VERSION);
	}
	
	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onCreate(android.database.sqlite
	 * .SQLiteDatabase)
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
        try {
            System.out.println("create ... " + DATABASE_TABLE);
          db.execSQL("CREATE TABLE " + DATABASE_TABLE + "(" +
	                    "_id INTEGER PRIMARY KEY autoincrement," +
	                    "time          VARCHAR, " +
	                    "effect_sound  VARCHAR, " +
		                "back_sound    VARCHAR);");
       } catch(Exception e) {
           db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);	
         onCreate(db);  	
       }
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see
	 * android.database.sqlite.SQLiteOpenHelper#onUpgrade(android.database.sqlite
	 * .SQLiteDatabase, int, int)
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG,
                "Upgrading " + DATABASE_TABLE + " database from version " +
                 oldVersion + " to " + newVersion +
                 ", which will destroy all old data");
        
         if(oldVersion != newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);	
            onCreate(db);  
         }
	}

	

   public ArrayList<HashMap<String, String>> getDataList(Context context) {

	   	mOpenHelper = new DBTifoData(context);
	    SQLiteDatabase db = mOpenHelper.getReadableDatabase();
	    	
		Cursor c = db.query( true, DATABASE_TABLE, new String[] {"time, effect_sound, back_sound"}, null, null, null, null, null, null);
		ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();

		try {
			int timeInx   		  = c.getColumnIndex("time");
			int effect_soundInx   = c.getColumnIndex("effect_sound");
			int back_soundInx     = c.getColumnIndex("back_sound");
			System.out.println("qrl = start");
		
			if (c.moveToFirst()) {
				do {
					HashMap<String, String> map = new HashMap();
					map.put("time", 		c.getString(timeInx));
					map.put("effect_sound", c.getString(effect_soundInx));
					map.put("back_sound", 	c.getString(back_soundInx));
					list.add(map);
					
					System.out.println("time = " + c.getString(timeInx));
				} while (c.moveToNext());
			}	
			System.out.println("qrl = end");
		} finally {
			if (c != null) c.close();
			if (db != null && db.isOpen()) {
				db.close();
			}
		}
		
		return list;
   }
   
   public void prcs(Context context, String type, String time, String effect_sound, String back_sound) throws Exception {
	   
	   mOpenHelper = new DBTifoData(context);
       SQLiteDatabase db = mOpenHelper.getReadableDatabase();
       
       try {
           if(type.equals("I")) {
                String insertMe = "INSERT INTO " + DATABASE_TABLE +  "(time, effect_sound, back_sound) " +  "VALUES ";
                insertMe = insertMe + "('"+ time +"','"+ effect_sound + "','"+ back_sound + "');";
                db.execSQL(insertMe);
           } else if(type.equals("U")) {
               String updateMe = "UPDATE " + DATABASE_TABLE +  " SET ";
               boolean bExist = false;
               if(!time.equals("")) {
            	   updateMe = updateMe + " time = '" +  time + "'";
            	   bExist = true;
               }
               if(!effect_sound.equals("")) {
            	   if(bExist) updateMe = updateMe +  " , ";
            	   updateMe = updateMe + " effect_sound = '" + effect_sound + "'"; 
               	   bExist = true;
               }
               if(!back_sound.equals("")) {
            	   if(bExist) updateMe = updateMe +  " , ";
            	   updateMe = updateMe + " back_sound = '" + back_sound + "'"; 
               }
               db.execSQL(updateMe);
           }
        } catch(Exception e) {
        	throw e;
        } finally {
        	try {
				if (db != null && db.isOpen()) {
					db.close();
				}
            } catch(Exception e) {
            	throw e;
            }
        }
    } 
   
   
    
}
